package com.ldq.imooc.basics.dataOperation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author : 李道泉
 * @Description :
 * @Date : 2018/5/5 16:18
 * @Modified by :
 */
public class TransInfoDao {

    public void add(TransInfo transInfo) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                "insert into trans_info (source_id,source_account,destination_id,destination_account,amount) values (?,?,?,?,?);";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setInt(1, transInfo.getSourceIid());
        ps.setString(2, transInfo.getSourceAaccount());
        ps.setInt(3, transInfo.getDestinationId());
        ps.setString(4, transInfo.getDestinationAccount());
        ps.setDouble(5, transInfo.getAmount());
        ps.execute();
    }

    public void delete(TransInfo transInfo) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                " delete from trans_info " +
                " where id = ?; ";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setInt(1, transInfo.getId());
        ps.execute();
    }

    public void modify(TransInfo transInfo) throws Exception {

        Connection coon = DBUtil.getConn();
        String sql = " " +
                " update trans_info set source_id=?,source_account=?,destination_id=?,destination_account=?,amount=? " +
                " where id = ? ; ";
        PreparedStatement ps = coon.prepareStatement(sql);

        ps.setInt(1, transInfo.getSourceIid());
        ps.setString(2, transInfo.getSourceAaccount());
        ps.setInt(3, transInfo.getDestinationId());
        ps.setString(4, transInfo.getDestinationAccount());
        ps.setDouble(5, transInfo.getAmount());
        ps.setInt(6, transInfo.getId());
        ps.execute();
    }

    public List<TransInfo> query(TransInfo transInfo) throws Exception {
        List<TransInfo> result = new ArrayList<TransInfo>();


        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from trans_info ");
        sb.append(" where account like ? ;");

        PreparedStatement ps = conn.prepareStatement(sb.toString());
        ps.setString(1, "%" + transInfo.getSourceAaccount() + "%");
        ResultSet rs = ps.executeQuery();

        TransInfo transIn = null;
        while (rs.next()) {
            transIn = new TransInfo();
            transIn.setId(rs.getInt("id"));
            transIn.setSourceAaccount(rs.getString("source_account"));
            transIn.setSourceIid(rs.getInt("source_id"));
            transIn.setDestinationAccount(rs.getString("destination_account"));
            transIn.setDestinationId(rs.getInt("destination_id"));
            transIn.setAmount(rs.getDouble("amount"));
            transIn.setCreateTime(rs.getDate("createTime"));

            result.add(transIn);
        }

        return result;
    }
}
